﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using CommonLibrary.DataModel.Basic;
using System.Data.SqlClient;
using CommonLibrary.DB;
using System.Data;

namespace ServiceApplication.Dao.Basic
{
    public class DeptDao
    {
        /// <summary>
        /// 
        /// </summary>
        /// <param name="deptEntity"></param>
        /// <returns>刚插入的主键</returns>
        public int AddEntity(DeptEntity deptEntity)
        {
            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@dept_id", deptEntity.dept_id));
            parameterList.Add(new SqlParameter("@dept_name", deptEntity.dept_name));
            parameterList.Add(new SqlParameter("@dept_supid", deptEntity.dept_supid));
            parameterList.Add(new SqlParameter("@dept_longid", deptEntity.dept_longid));
            parameterList.Add(new SqlParameter("@dept_isroot", deptEntity.dept_isroot));
            parameterList.Add(new SqlParameter("@dept_isleaf", deptEntity.dept_isleaf));
            parameterList.Add(new SqlParameter("@dept_isfather", deptEntity.dept_isfather));
            parameterList.Add(new SqlParameter("@dept_descn", deptEntity.dept_descn));
            parameterList.Add(new SqlParameter("@dept_status", deptEntity.dept_status));
            parameterList.Add(new SqlParameter("@dept_catalogy", deptEntity.dept_catalogy));


            string sql = "insert into jc_dept (dept_name,dept_supid,dept_longid,dept_isroot,dept_isleaf,dept_isfather,dept_descn, dept_status,dept_catalogy) values " +
                " (@dept_name,@dept_supid,@dept_longid,@dept_isroot,@dept_isleaf,@dept_isfather,@dept_descn,@dept_status,@dept_catalogy); " +
                " select @@identity";
            string dept_id = DBExecuteUtil.getUniqueResult(sql, parameterList.ToArray());
            return int.Parse(dept_id);
        }

        public void DleteEntity(int dept_id)
        {
            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@dept_id", dept_id));
            string sql = "delete from jc_dept where dept_id=@dept_id ";
            DBExecuteUtil.executeSqlWithPar(sql, parameterList.ToArray());
        }

        public void UpdateEntity(DeptEntity deptEntity)
        {
            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@dept_id", deptEntity.dept_id));
            parameterList.Add(new SqlParameter("@dept_name", deptEntity.dept_name));
            parameterList.Add(new SqlParameter("@dept_supid", deptEntity.dept_supid));
            parameterList.Add(new SqlParameter("@dept_longid", deptEntity.dept_longid));
            parameterList.Add(new SqlParameter("@dept_isroot", deptEntity.dept_isroot));
            parameterList.Add(new SqlParameter("@dept_isleaf", deptEntity.dept_isleaf));
            parameterList.Add(new SqlParameter("@dept_isfather", deptEntity.dept_isfather));
            parameterList.Add(new SqlParameter("@dept_descn", deptEntity.dept_descn));
            parameterList.Add(new SqlParameter("@dept_status", deptEntity.dept_status));
            parameterList.Add(new SqlParameter("@dept_catalogy", deptEntity.dept_catalogy));

            string sql = "update jc_dept set dept_name=@dept_name,dept_supid=@dept_supid,dept_longid=@dept_longid,dept_isroot=@dept_isroot," +
                " dept_isleaf=@dept_isleaf,dept_isfather=@dept_isfather,dept_descn=@dept_descn, dept_status=@dept_status,dept_catalogy=@dept_catalogy where dept_id=@dept_id";

            DBExecuteUtil.executeSqlWithPar(sql, parameterList.ToArray());
        }

        public DeptEntity QueryEntity(int dept_id)
        {
            DeptEntity deptEntity = null;
            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@dept_id", dept_id));
            string sql = "select dept_id,dept_name,dept_supid,dept_longid,dept_isroot,dept_isleaf,dept_isfather,dept_descn, dept_status,dept_catalogy " +
                " from jc_dept where dept_id=@dept_id";

            var result = DBExecuteUtil.querySqlArray(sql, parameterList.ToArray());
            if (result == null) return null;

            deptEntity = new DeptEntity();
            deptEntity.dept_id = int.Parse(result[0][0]);
            deptEntity.dept_name = result[0][1];
            deptEntity.dept_supid = int.Parse(result[0][2]);
            deptEntity.dept_longid = result[0][3];
            deptEntity.dept_isroot = int.Parse(result[0][4]);
            deptEntity.dept_isleaf = int.Parse(result[0][5]);
            deptEntity.dept_isfather = int.Parse(result[0][6]);
            deptEntity.dept_descn = result[0][7];
            deptEntity.dept_status = result[0][8];
            deptEntity.dept_catalogy = result[0][9];
            return deptEntity;
        }

        public DeptEntity QueryEntity(string dept_name)
        {
            DeptEntity deptEntity = null;
            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@dept_name", dept_name));
            string sql = "select dept_id,dept_name,dept_supid,dept_longid,dept_isroot,dept_isleaf,dept_isfather,dept_descn, dept_status,dept_catalogy " +
                " from jc_dept where name=@dept_name";

            var result = DBExecuteUtil.querySqlArray(sql, parameterList.ToArray());
            if (result == null) return null;

            deptEntity = new DeptEntity();
            deptEntity.dept_id = int.Parse(result[0][0]);
            deptEntity.dept_name = result[0][1];
            deptEntity.dept_supid = int.Parse(result[0][2]);
            deptEntity.dept_longid = result[0][3];
            deptEntity.dept_isroot = int.Parse(result[0][4]);
            deptEntity.dept_isleaf = int.Parse(result[0][5]);
            deptEntity.dept_isfather = int.Parse(result[0][6]);
            deptEntity.dept_descn = result[0][7];
            deptEntity.dept_status = result[0][8];
            deptEntity.dept_catalogy = result[0][9];
            return deptEntity;
        }

        public DataTable QueryAllDepts()
        {
            string sql = " select dept_id 部门编码,dept_name 部门名称,dept_supid 上级编码,dept_longid 上级编码组,dept_isroot 是否根部门,dept_isleaf 是否叶部门, " +
                             " dept_isfather 是否父部门,dept_descn 描述,dept_status 状态,dept_catalogy 分类 " +
                             " from jc_dept ";

            return DBExecuteUtil.querySqlTable(sql);
        }



    }
}
